Partitioning¶

Partitioning key¶

The partitioning key is an expression which is used by the partitioning method to distribute data across defined partitions. The following data types are supported for the partitioning key:

• CHAR
• VARCHAR
• SMALLINT
• INT
• BIGINT
• DATE
• TIME
• TIMESTAMP
• DATETIME

The following restrictions apply to the partitioning key:

• The partitioning key must use exactly one column from the partitioned table.

• Aggregate functions, analytic functions, logical operators and comparison operators are not allowed in the partitioning key expression.

• The following functions and expressions are not allowed in the partitioning key expression:

  • CASE
  • CHARSET()
  • CHR()
  • COALESCE()
  • SERIAL_CURRENT_VALUE()
  • SERIAL_NEXT_VALUE()
  • DECODE()
  • DECR()
  • INCR()
  • DRAND()
  • DRANDOM()
  • GREATEST()
  • LEAST()
  • IF()
  • IFNULL()
  • INSTR()
  • NVL()
  • NVL2()
  • ROWNUM
  • INST_NUM()
  • USER
  • PRIOR
  • WIDTH_BUCKET()

Range Partitioning¶

Range partitioning is a partitioning method in which a table is partitioned using an user specified range of values of the partitioning key for each partition. Ranges are defined as continuous non-overlapping intervals. This partitioning method is most useful when table data can be divided into range intervals (e.g. order placement date for an orders table or age intervals for an users table). Range partitioning is the most versatile partitioning method in terms of Partition Pruning because almost all search predicates can be used to identify matching ranges.

Tables can be partitioned by range by using the PARTITION BY RANGE clause in CREATE or ALTER statements.

CREATE TABLE table_name (
   ...
)
PARTITION BY RANGE ( <partitioning_key> ) (
   PARTITION partition_name VALUES LESS THAN ( <range_value> ),
   PARTITION partition_name VALUES LESS THAN ( <range_value> ),
   ...
)

ALTER TABLE table_name PARTITION BY RANGE ( <partitioning_key> ) (
   PARTITION partition_name VALUES LESS THAN ( <range_value> ),
   PARTITION partition_name VALUES LESS THAN ( <range_value> ),
   ...
)

• partitioning_key : Specifies the Partitioning key.
• partition_name : Specifies the partition name.
• range_value : Specifies the upper limit of the partitioning key value. All tuples for which the evaluation of partitioning key is less than (but not equal to) the range_value will be stored in this partition.

The following example shows how to create the participant2 table which holds countries participating at the Olympics and partition this table into partitions holding participants before year 2000(before_2000 partition) and participants before year 2008(before_2008 partition):

CREATE TABLE participant2 (host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT)
PARTITION BY RANGE (host_year) (
  PARTITION before_2000 VALUES LESS THAN (2000),
  PARTITION before_2008 VALUES LESS THAN (2008)
);

When creating partitions, CUBRID sorts the user supplied range values from smallest to largest and creates the non-overlapping intervals from the sorted list. The identifier MAXVALUE can be used to specify an infinite upper limit for a partition. In the example above, the created range intervals are [-∞, 2000) and [2000, 2008).

When inserting a tuple into a range-partitioned table, CUBRID identifies the range to which the tuple belongs by evaluating the partitioning key. If the partitioning key value is NULL, the data is stored in the partition with the smallest specified range value. If there is no range which would accept the partitioning key value, CUBRID returns an error. CUBRID also returns an error when updating a tuple if the new value of the partitioning key does not belong to any of the defined ranges.

Hash Partitioning¶

Hash partitioning is a partitioning method which is used to distribute data across a specified number of partition. This partitioning method is useful when table data contains values for which ranges or lists would be meaningless (for example, a keywords table or an users table for which user_id is the most interesting value). If the values for the partitioning key are evenly distributed across the table data, hash-partitioning technique divides table data evenly between the defined partitions. For hash partitioning, Partition Pruning can only be applied on equality predicates (e.g. predicates using = and IN expressions), making hash partitioning useful only if most of the queries specify such a predicate for the partitioning key.

Tables can be partitioned by hash by using the PARTITION BY HASH clause in CREATE or ALTER statements:

CREATE TABLE table_name (
   ...
)
PARTITION BY HASH ( <partitioning_key> )
PARTITIONS ( number_of_partitions )

ALTER TABLE table_name
PARTITION BY HASH (<partitioning_key>)
PARTITIONS (number_of_partitions)

• partitioning_key : Specifies the Partitioning key.
• number_of_partitions : Specifies the number of partitions to be created.

The following example shows how to create the nation2 table with country code and country names, and define 4 hash partitions based on code values. Only the number of partitions, not the name, is defined in hash partitioning.

CREATE TABLE nation2 (
  code CHAR (3),
  name VARCHAR (50)
)
PARTITION BY HASH (code) PARTITIONS 4;

When a value is inserted into a hash-partitioned table, the partition to store the data is determined by the hash value of the partitioning key. If the partitioning key value is NULL, the data is stored in the first partition.

List Partitioning¶

List partitioning is a partitioning method in which a table is divided into partitions according to user specified list of values for the partitioning key. The lists of values for partitions must be disjoint sets. This partitioning method is useful when table data can be divided into lists of possible values which have a certain meaning (e.g. department id for an employees table or country code for an users table). As for hash partitioning, Partition Pruning for list partitioned tables can only be applied on equality predicates (e.g. predicates using = and IN expressions).

Tables can be partitioned by list by using the PARTITION BY LIST clause in CREATE or ALTER statements:

CREATE TABLE table_name (
  ...
)
PARTITION BY LIST ( <partitioning_key> ) (
  PARTITION partition_name VALUES IN ( <values_list> ),
  PARTITION partition_name VALUES IN ( <values_list> ),
  ...
)

ALTER TABLE table_name
PARTITION BY LIST ( <partitioning_key> ) (
  PARTITION partition_name VALUES IN ( <values_list> ),
  PARTITION partition_name VALUES IN ( <values_list> ),
  ...
)

• partitioning_key : Specifies the Partitioning key.
• partition_name : Specifies the partition name.
• value_list : Specifies the list of values for the partitioning key.

The following example shows how to create the athlete2 table with athlete names and sport events, and define list partitions based on event values.

CREATE TABLE athlete2 (name VARCHAR (40), event VARCHAR (30))
PARTITION BY LIST (event) (
    PARTITION event1 VALUES IN ('Swimming', 'Athletics'),
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing'),
    PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball')
);

When inserting a tuple into a list-partitioned table, the value of the partitioning key must belong to one of the value lists defined for partitions. For this partitioning model, CUBRID does not automatically assign a partition for NULL values of the partitioning key. To be able to store NULL values into a list-partitioned table, a partition which includes the NULL value in the values list must be created:

CREATE TABLE athlete2 (name VARCHAR (40), event VARCHAR (30))
PARTITION BY LIST (event) (
    PARTITION event1 VALUES IN ('Swimming', 'Athletics' ),
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing'),
    PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball', NULL)
);

Partition Pruning¶

Partition pruning is an optimization, limiting the scope of a query on a partitioned table by eliminating partitions. During partition pruning, CUBRID examines the WHERE clause of the query to identify partitions for which this clause would be always false (considering to the way partitioning was defined). In the following example, the SELECT query will only be applied to partitions before_2008 and before_2012, since CUBRID knows that the rest of partitions hold data for which YEAR (opening_date) is less than 2004.

CREATE TABLE olympic2 (opening_date DATE, host_nation VARCHAR (40))
PARTITION BY RANGE (YEAR(opening_date)) (
    PARTITION before_1996 VALUES LESS THAN (1996),
    PARTITION before_2000 VALUES LESS THAN (2000),
    PARTITION before_2004 VALUES LESS THAN (2004),
    PARTITION before_2008 VALUES LESS THAN (2008),
    PARTITION before_2012 VALUES LESS THAN (2012)
);

SELECT opening_date, host_nation
FROM olympic2
WHERE YEAR(opening_date) > 2004;

Partition pruning greatly reduces the disk I/O and the amount of data which must be processed during query execution. It is important to understand when pruning is performed in order to fully benefit from it. In order for CUBRID to successfully prune partitions, the following conditions have to be met:

• Partitioning key must be used in the WHERE clause directly (without applying other expressions to it)
• For range-partitioning, the partitioning key must be used in range predicates (<, >, BETWEEN, etc) or equality predicates (=, IN, etc).
• For list and hash partitioning, the partitioning key must be used in equality predicates (=, IN, etc).

The following queries explain how pruning is performed on the olympic2 table from the example above:

-- prune all partitions except before_2012
SELECT host_nation
FROM olympic2
WHERE YEAR (opening_date) >= 2008;

-- prune all partitions except before_2008
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) BETWEEN 2005 and 2007;

-- no partition is pruned because partitioning key is not used
SELECT host_nation
FROM olympic2
WHERE opening_date = '2008-01-02';

-- no partition is pruned because partitioning key is not used directly
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) + 1 = 2008;

-- no partition is pruned because there is no useful predicate in the WHERE clause
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) != 2008;

In versions older than CUBRID 9.0, partition pruning was performed during query compilation stage. Starting with CUBRID 9.0, partition pruning is performed during the query execution stage. Executing partition pruning during query execution allows CUBRID to apply this optimization on much more complex queries. However, pruning information is not displayed in query plans anymore (since query planning happens before query execution and this information is not available at that time).

Users can also access partitions directly (independent of the partitioned table) either by using the table name assigned by CUBRID to a partition or by using the table PARTITION (name) clause:

-- to specify a partition with its table name
SELECT * FROM olympic2__p__before2008;

-- to specify a partition with PARTITION clause
SELECT * FROM olympic2 PARTITION (before2008);

Both of the queries above access partition before2008 as if it were a normal table (not a partition). This is a very useful feature because it allows certain query optimizations to be used even though they are disabled on partitioned tables (see Notes on Partitioning for more info). Users should note that, when accessing partitions directly, the scope of the query is limited to that partition. This means that tuples from other partitions are not considered (even though the WHERE clause would include them) and, for INSERT and UPDATE statements, if the tuple inserted/updated does not belong to the specified partition, an error is returned.

By executing queries on a partition rather than the partitioned table, some of the benefits of partitioning are lost. For example, if users only execute queries on the partitioned table, this table can be repartitioned or partitions can be dropped without having to modify the user application. If users access partitions directly, this benefit is lost. Users should also note that, even though using partitions in INSERT statements is allowed (for consistency), it is discouraged because there is no performance gain from it.

Partitioning Management¶

Partitioned tables can be managed using partition specific clauses of the ALTER statement. CUBRID allows several actions to be performed on partitions:

1. Modifying a partitioned table into a regular table.
2. Partitions reorganization.
3. Adding partitions to an already partitioned table.
4. Dropping partitions.
5. Promote partitions to regular tables.

ALTER {TABLE | CLASS} table_name REMOVE PARTITIONING

ALTER {TABLE | CLASS} table_name
REORGANIZE PARTITION <alter_partition_name_comma_list>
INTO ( <partition_definition_comma_list> )

partition_definition_comma_list ::=
PARTITION partition_name VALUES LESS THAN ( <range_value> ), ...

ALTER TABLE participant2
REORGANIZE PARTITION before_2000 INTO (
  PARTITION before_1996 VALUES LESS THAN (1996),
  PARTITION before_2000 VALUES LESS THAN (2000)
);

ALTER TABLE participant2
REORGANIZE PARTITION before_1996, before_2000 INTO (
  PARTITION before_2000 VALUES LESS THAN (2000)
);

ALTER TABLE athlete2
REORGANIZE PARTITION event2 INTO (
    PARTITION event2_1 VALUES IN ('Judo'),
    PARTITION event2_2 VALUES IN ('Taekwondo', 'Boxing')
);

ALTER TABLE athlete2
REORGANIZE PARTITION event2_1, event2_2 INTO (
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing')
);

ALTER {TABLE | CLASS} table_name
ADD PARTITION (<partition_definitions_comma_list>)

ALTER TABLE participant2 ADD PARTITION (
  PARTITION before_2012 VALUES LESS THAN (2012),
  PARTITION last_one VALUES LESS THAN MAXVALUE
);

ALTER {TABLE | CLASS} table_name
DROP PARTITION partition_name_list

ALTER TABLE participant2 DROP PARTITION before_2000;

ALTER {TABLE | CLASS} table_name
COALESCE PARTITION number_of_shrinking_partitions

ALTER TABLE nation2 COALESCE PARTITION 1;

ALTER {TABLE | CLASS} table_name
ADD PARTITION PARTITIONS number

ALTER TABLE nation2 ADD PARTITION PARTITIONS 3;

ALTER TABLE table_name PROMOTE PARTITION <partition_name_list>

CREATE TABLE t (i INT) PARTITION BY LIST (i) (
    PARTITION p0 VALUES IN (1, 2),
    PARTITION p1 VALUES IN (3, 4),
    PARTITION p2 VALUES IN (5, 6)
);

INSERT INTO t VALUES(1), (2), (3), (4), (5), (6);

csql> ;schema t
=== <Help: Schema of a Class> ===
...
 <Partitions>
     PARTITION BY LIST ([i])
     PARTITION p0 VALUES IN (1, 2)
     PARTITION p1 VALUES IN (3, 4)
     PARTITION p2 VALUES IN (5, 6)

csql> SELECT * FROM t;

=== <Result of SELECT Command in Line 1> ===
            i
=============
            1
            2
            3
            4
            5
            6

ALTER TABLE t PROMOTE PARTITION p0, p2;

csql> ;schema t
=== <Help: Schema of a Class> ===
 <Class Name>
     t
 ...
 <Partitions>
     PARTITION BY LIST ([i])
     PARTITION p1 VALUES IN (3, 4)

csql> SELECT * FROM t;

=== <Result of SELECT Command in Line 1> ===
            i
=============
            3
            4

Indexes on Partitioned Tables¶

Indexes created on a partitioning table are either local or global indexes. Global Index store data from all partitions while, with local indexes, data for each partition is stored in a separate(local) index. When creating an index on a partitioned table, CUBRID decides whether that index will be local or global applying the following rules:

• Primary keys are always global indexes.
• Foreign keys are always local indexes.
• All non-unique indexes are local.
• An unique index is local only if the partitioning key is part of the index definition.

The following examples show how CUBRID decides between local and global indexes:

CREATE TABLE t(i INTEGER, j INTEGER k INTEGER)
PARTITION BY HASH(i) PARTITIONS 5;

-- pk_t_i is global because it is a primary key
ALTER TABLE t ADD CONSTRAINT pk_t_i PRIMARY KEY(i);

-- i_t_j and i_t_j_k are local indexes
CREATE INDEX i_t_j ON t(j);
CREATE INDEX i_t_j_k ON t(j, k);

-- u_t_i_j is a local index because the partitioning key (i) is part of the index definition
CREATE UNIQUE INDEX u_t_i_j ON t(i, j);

-- u_t_j_k is a global index because the partitioning key (i) is not part of the index definition
CREATE UNIQUE INDEX u_t_j_k ON t(j, k);

It is important to define local indexes wherever possible. CUBRID does not optimize index scans to be able to scan several partitions together using a global index. Instead, in a global index scan, for each partition that was not pruned a separate index scan is performed. This leads to poorer performance than scanning local indexes because data from other partitions is fetched from disk and then discarded (it belongs to another partition than the one being scanned at the moment). INSERT statements also show better performance on local indexes since these indexes are smaller.

Notes on Partitioning¶

Partitioned tables normally behave like regular tables. However there are some notes that should be taken into consideration in order to fully benefit from partitioning a table.

CREATE TABLE t (c CHAR(50) COLLATE utf8_bin)
PARTITION BY LIST (c) (
    PARTITION p0 VALUES IN (_utf8'x'),
    PARTITION p1 VALUES IN (_iso88591'y')
);

CREATE TABLE tbl (str STRING) COLLATE utf8_en_ci
PARTITION BY LIST (str) (
    PARTITION p0 VALUES IN ('test'),
    PARTITION p1 VALUES IN ('TEST')
);

ERROR: Partition definition is duplicated. 'p1'